package domain.service.charge;

import domain.dao.charge.ChargeDao;
import domain.dao.rate.RateInfoDao;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2017/9/6.
 */
@Service
public class ChargeService {
    @Autowired
    private ChargeDao chargeDao;

    /**
     * 获取月度投资数据
     * @param year
     */
    public Map<String,Object> getMonthMoney(String year){
        Map<String,Object> resultMap = new HashMap<String, Object>();
        String sql = "";
        /***保存直接工程数据**/
        List<Double> directMoneyArray = new ArrayList<Double>();
        /***保存动迁数据**/
        List<Double> resettlementMoneyArray = new ArrayList<Double>();
        /***保存其他数据**/
        List<Double> otherMoneyArray = new ArrayList<Double>();
        /*****保存月度累计投资*****/
        List<Double> sumMoneyArray = new ArrayList<Double>();

//        select NVL(sum(THISMONTHFINISH), 0),dt from charge
//        where SUBSTR(dt,0,4) = '2016' and typename in('一标段','二标段','三标段')  group by dt order by dt desc;
//
//        select NVL(sum(THISMONTHFINISH), 0),dt from charge
//        where SUBSTR(dt,0,4) = '2016' and typename in('动迁费用')  group by dt order by dt desc;
//
//        select NVL(sum(THISMONTHFINISH), 0),dt from charge
//        where SUBSTR(dt,0,4) = '2016' and typename in('其它费用')  group by dt order by dt desc;

//        select NVL(sum(THISMONTHFINISH), 0),dt from charge
//        where SUBSTR(dt,0,4) = '2016' and typename in('一标段','二标段','三标段','动迁费用','其它费用')  group by dt order by dt desc;
//
//        select NVL(sum(THISMONTHFINISH), 0) from charge
//        where TO_NUMBER（SUBSTR(dt,0,4))< 2017 and typename in('一标段','二标段','三标段','动迁费用','其它费用') ;

//        select NVL(sum(THISMONTHFINISH), 0) from charge
//        where TO_NUMBER（SUBSTR(dt,0,4))= 2017 and SUBSTR(dt,INSTR(dt,'-',1,1)+1,2)<3 and typename in('一标段','二标段','三标段','动迁费用','其它费用') ;

        /************直接工程费用月度统计 一标段**********/
        List<Double> oneMoneyArray = new ArrayList<Double>();
        List<Double> twoMoneyArray = new ArrayList<Double>();
        List<Double> threeMoneyArray = new ArrayList<Double>();
        for( int i =1;i<13;i++){
            String month = year+"-"+i;
            /***查询直接工程费用***/
             sql = " select NVL(sum(THISMONTHFINISH), 0) from charge where  dt = '"+month+"' and (typename = '一标段' or typename = '二标段' or typename = '三标段')  ";
            Double directMoney = chargeDao.executeSql(sql);
            directMoneyArray.add(directMoney);

            /***查询动迁费用***/
            sql = " select NVL(sum(THISMONTHFINISH), 0) from charge where  dt = '"+month+"' and typename = '动迁费用'  ";
            Double resettlementMoney = chargeDao.executeSql(sql);
            resettlementMoneyArray.add(resettlementMoney);

            /***查询其他费用***/
            sql = " select NVL(sum(THISMONTHFINISH), 0) from charge where  dt = '"+month+"' and typename = '其它费用'  ";
            Double otherMoney = chargeDao.executeSql(sql);
            otherMoneyArray.add(otherMoney);

            sql = " select NVL(sum(THISMONTHFINISH), 0) from charge where  dt <= '"+month+"' and (typename = '一标段' or typename = '二标段' or typename = '三标段' or typename = '动迁费用' or typename = '其它费用')   ";
            Double sumMoney = chargeDao.executeSql(sql);
            sumMoneyArray.add(sumMoney);

            /************直接工程费用月度统计**********/
            sql = " select NVL(sum(THISMONTHFINISH), 0) from charge where  dt = '"+month+"' and typename = '一标段'  ";
            Double oneMoney = chargeDao.executeSql(sql);
            oneMoneyArray.add(oneMoney);

            sql = " select NVL(sum(THISMONTHFINISH), 0) from charge where  dt = '"+month+"' and typename = '二标段'  ";
            Double twoMoney = chargeDao.executeSql(sql);
            twoMoneyArray.add(twoMoney);

            sql = " select NVL(sum(THISMONTHFINISH), 0) from charge where  dt = '"+month+"' and typename = '三标段'  ";
            Double threeMoney = chargeDao.executeSql(sql);
            threeMoneyArray.add(threeMoney);
        }
        resultMap.put("data1",directMoneyArray);
        resultMap.put("data2",resettlementMoneyArray);
        resultMap.put("data3",otherMoneyArray);
        resultMap.put("data4",sumMoneyArray);
        resultMap.put("oneMoneyArray",oneMoneyArray);
        resultMap.put("twoMoneyArray",twoMoneyArray);
        resultMap.put("threeMoneyArray",threeMoneyArray);
        return resultMap;
    }
}
